PivotTable and PivotChart Wizard

Use the PivotTable and PivotChart Wizard to create PivotTable reports and PivotChart reports.

Creates a PivotTable or PivotChart report from data organized in labeled rows and columns on a Microsoft Excel worksheet.

External data source.

Creates a PivotTable or PivotChart report from data stored in a file or database outside the current workbook or Microsoft Excel.

Multiple consolidation ranges.

Creates one PivotTable or PivotChart report from multiple Microsoft Excel worksheet ranges.

Another PivotTable report or PivotChart report.

Creates a PivotTable or PivotChart report from another PivotTable report in the same workbook.

Show Specify the type or report to create

PivotTable

Creates a PivotTable report.

PivotChart report (with PivotTable report).

Creates a PivotChart report and an associated PivotTable report that shares the same data and reflects all changes to the PivotChart report. A PivotChart report must have an associated PivotTable report in the same workbook.

When you create a PivotTable or PivotChart report from multiple consolidation ranges, you can use page fields to display data for one item independent of other items in a field. When you select an item in a page field, the data shown in the PivotTable or PivotChart report changes.

Create a single page for me.

Creates only one page field for a PivotTable or PivotChart report that you create from multiple Microsoft Excel consolidation ranges. With a single page field, you can view and combine all of your consolidated ranges with a drop-down list.

I will create the page fields.

Customizes page fields for a PivotTable or PivotChart report that you create from multiple Microsoft Excel consolidation ranges. You can assign separate names to your ranges in as many as four page fields.

Range

Specify the range for each consolidation source you want in your PivotTable or PivotChart report. Type the sheet name and range reference in the Range box. The Collapse Dialog button at the right end of this box temporarily moves the dialog box so that you enter the range by selecting cells on the worksheet. When you finish, you can click the button again to display the entire dialog box. If the range is from another workbook, type the workbook name and sheet name in the Range box, using this syntax: ([workbookname]sheetname!range) Click Add to enter the reference in the All ranges box. Repeat this procedure for each range you want to consolidate in your PivotTable or PivotChart report.

All ranges

Lists all the worksheet ranges that will be consolidated or combined in your PivotTable or PivotChart report.

Specify the location of your PivotTable report. If you are creating a PivotChart report, an associated PivotTable report will be created in the location you specify. This PivotTable report must be in the same workbook as the PivotChart report. If you specify a location in another workbook, the PivotChart report will also be created in that workbook. To set advanced options, click Options.

Layout

Click to lay out the PivotTable or PivotChart report in a dialog box rather than using on-sheet layout with certain external database sources, including large databases. For example, data retrieval is likely to be slow if you use on-sheet layout for a report based on a cube created by using the OLAP Cube Wizard in Microsoft Query. You can also use this dialog box to change the page field settings to create page fields that retrieve data for each item separately. Page field options are available only for reports based on external non-OLAP source data.

Options

Sets advanced formatting, layout, memory management, and external data options for your PivotTable or PivotChart report.